﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using ZLDC.Common.Estate;
using System.Data;

namespace ZLDC.DAL.Estate
{
    public class HousingInformDAL
    {
                                public static void p_housingInform_save(int ID, int housingId, string reason, string advise, int informDeptId, string informDept, int informUserId, string informUser, int dDeptId, string dDept, int dUserId, string dUser, string dView, string dResult, int dltFlag, int isValid, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@reason", reason));
            parms.Add(new SqlParameter("@advise", advise));
            parms.Add(new SqlParameter("@informDeptId", informDeptId));
            parms.Add(new SqlParameter("@informDept", informDept));
            parms.Add(new SqlParameter("@informUserId", informUserId));
            parms.Add(new SqlParameter("@informUser", informUser));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));
            parms.Add(new SqlParameter("@dView", dView));
            parms.Add(new SqlParameter("@dResult", dResult));
            parms.Add(new SqlParameter("@dltFlag", dltFlag));
            parms.Add(new SqlParameter("@isValid", isValid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingInform_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                        
        public static DataTable p_housingInform_list_table(int page, int pagesize, int housingId, int isValid, int deptid, string IdList, string rolename, string duserid, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@housingId", housingId));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@deptid", deptid));
            parms.Add(new SqlParameter("@IdList", IdList));
            parms.Add(new SqlParameter("@rolename", rolename));
            parms.Add(new SqlParameter("@duserid", duserid));    

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_housingInform_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                                                                                                                        public static void p_housingInform_update(int ID, int dDeptId, string dDept, int dUserId, string dUser, string dView, string dResult, int isValid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@dUser", dUser));
            parms.Add(new SqlParameter("@dView", dView));
            parms.Add(new SqlParameter("@dResult", dResult));
            parms.Add(new SqlParameter("@isValid", isValid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingInform_update", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                                public static void p_housingInform_updateisValid(int ID, int isValid,string deptid,string deptname,string userid,string username, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@approverdeptid", deptid));
            parms.Add(new SqlParameter("@approverdept", deptname));
            parms.Add(new SqlParameter("@approverid", userid));
            parms.Add(new SqlParameter("@approver", username));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingInform_updateisValid", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                                                                                                        public static void p_housingInform_updateisResult(int ID, int dDeptId, string dDept, int dUserId, string duser, string dResult, int isValid, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@dDeptId", dDeptId));
            parms.Add(new SqlParameter("@dDept", dDept));
            parms.Add(new SqlParameter("@dUserId", dUserId));
            parms.Add(new SqlParameter("@duser", duser));
            parms.Add(new SqlParameter("@dResult", dResult));
            parms.Add(new SqlParameter("@isValid", isValid));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housingInform_updateisResult", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
                public static DataTable p_housingInform_ResultList_table(int page, int pagesize, int isValid, string dResult, string IdList,string DeptName,string starttime,string endtime, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@isValid", isValid));
            parms.Add(new SqlParameter("@dResult", dResult));
            parms.Add(new SqlParameter("@IdList", IdList));
            parms.Add(new SqlParameter("@deptname",DeptName));
            parms.Add(new SqlParameter("@starttime", starttime));
            parms.Add(new SqlParameter("@endtime", endtime));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("[p_housingInform_ResultList]", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
                public static void p_housing_updateisValid(int ID, string advise, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@ID", ID));
            parms.Add(new SqlParameter("@advise", advise));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_housing_updateisValid", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                public static int GetHousingIDById(int id) 
        {
            string SQL = "select housingId from t_housingInform where  ID=" + id + "";
            DataSet ds=null;
            int hid = 0;
            using(_Database db=new _Database ("estate"))
            {
                 ds=db.Query(SQL);
            }
            if (ds.Tables[0].Rows.Count>0) 
            {
                hid =_Convert.ToInt(ds.Tables[0].Rows[0]["housingid"].ToString());
            }
            return hid;
        }
    }
}
